package com.meilai.project.service.finance.contractManage.impl;

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.meilai.project.constant.BossUserEnum;
import com.meilai.project.constant.MessageCenterTypeEnum;
import com.meilai.project.controller.web.adMake.service.BillAdMakeSupplierConfirmService;
import com.meilai.project.controller.web.adMake.vo.BillAdMakeSupplierConfirmVO;
import com.meilai.project.entity.administration.goods.GoodsPutStorage;
import com.meilai.project.entity.business.contract.ContractApply;
import com.meilai.project.entity.business.contract.ContractPayDetail;
import com.meilai.project.entity.business.customer.CustomerInfo;
import com.meilai.project.entity.business.sale.BillSaleGroup;
import com.meilai.project.entity.business.sale.BillSaleGroupRelation;
import com.meilai.project.entity.business.sale.BillSaleProxy;
import com.meilai.project.entity.business.saleOrder.BillSaleAppendix;
import com.meilai.project.entity.finance.gear.Gear;
import com.meilai.project.entity.finance.invoice.InvoiceApply;
import com.meilai.project.entity.finance.paymentReceived.AccountReceived;
import com.meilai.project.entity.system.Dictionary;
import com.meilai.project.entity.system.MessageCenter;
import com.meilai.project.exception.CommonException;
import com.meilai.project.mapper.finance.contractManage.FinancialSummaryMapper;
import com.meilai.project.service.administration.goods.GoodsPutStorageService;
import com.meilai.project.service.business.contract.ContractApplyService;
import com.meilai.project.service.business.customer.CustomerInfoService;
import com.meilai.project.service.business.sale.BillSaleGroupRelationService;
import com.meilai.project.service.business.sale.BillSaleGroupService;
import com.meilai.project.service.business.sale.BillSaleService;
import com.meilai.project.service.business.saleOrder.BillSaleAppendixService;
import com.meilai.project.service.finance.contractManage.FinancialSummaryService;
import com.meilai.project.service.finance.invoice.InvoiceApplyService;
import com.meilai.project.service.finance.paymentReceived.AccountReceivedService;
import com.meilai.project.service.finance.reimburse.ReimburseApplyService;
import com.meilai.project.service.gear.GearService;
import com.meilai.project.service.system.DictionaryService;
import com.meilai.project.service.system.MessageCenterService;
import com.meilai.project.util.DateUtil;
import com.meilai.project.vo.business.contract.ContractApplyVO;
import com.meilai.project.vo.business.sale.BillSaleMediaAmountVO;
import com.meilai.project.vo.business.sale.BillSaleVO;
import com.meilai.project.vo.business.sale.CustomerCommissionVO;
import com.meilai.project.vo.business.sale.MediaSpecTypeCountAmountVO;
import com.meilai.project.vo.finance.contractManage.ContractBillSaleGroupVO;
import com.meilai.project.vo.finance.contractManage.MediaPointVO;
import com.meilai.project.vo.finance.reimburse.ReimburseApplyVO;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
public class FinancialSummaryServiceImpl implements FinancialSummaryService {

    @Autowired
    private FinancialSummaryMapper financialSummaryMapper;

    @Autowired
    private AccountReceivedService accountReceivedService;

    @Autowired
    private GoodsPutStorageService goodsPutStorageService;

    @Autowired
    private InvoiceApplyService invoiceApplyService;

    @Autowired
    private ContractApplyService contractApplyService;

    @Autowired
    private BillSaleService billSaleService;

    @Autowired
    private BillSaleGroupRelationService billSaleGroupRelationService;

    @Autowired
    private DictionaryService dictionaryService;

    @Autowired
    private ReimburseApplyService reimburseApplyService;

    @Autowired
    private BillAdMakeSupplierConfirmService billAdMakeSupplierConfirmService;

    @Autowired
    private GearService gearService;

    @Autowired
    private BillSaleGroupService billSaleGroupService;

    @Autowired
    private MessageCenterService messageCenterService;

    @Autowired
    private CustomerInfoService customerInfoService;

    @Autowired
    private BillSaleAppendixService billSaleAppendixService;

    @Override
    public Page<ContractBillSaleGroupVO> list(Integer current_page, Integer pageSize, Long customerId, String contractNum, LocalDate contractBeginDateStart, LocalDate contractBeginDateEnd, LocalDate contractEndDateStart, LocalDate contractEndDateEnd) {
        Page<ContractBillSaleGroupVO> page = new Page<>(current_page, pageSize);
        // 查询分页
        List<ContractBillSaleGroupVO> list = financialSummaryMapper.list(page, customerId, contractNum, contractBeginDateStart, contractBeginDateEnd, contractEndDateStart, contractEndDateEnd);
        List<ContractBillSaleGroupVO> resultList = new ArrayList<>();
        handle(list, resultList);
        return page.setRecords(resultList);
    }

    private void handle(List<ContractBillSaleGroupVO> list, List<ContractBillSaleGroupVO> resultList) {
        List<Long> boss_ids = new ArrayList<>(Arrays.asList(BossUserEnum.values())).stream().map(BossUserEnum::getId).collect(Collectors.toList());
        if (CollectionUtils.isNotEmpty(list)) {
            List<Long> ids = list.stream().map(ContractBillSaleGroupVO::getId).collect(Collectors.toList());
            // 获取属性
            List<ContractBillSaleGroupVO> result = financialSummaryMapper.listByIds(ids);
            List<Long> collect4 = result.stream().map(ContractBillSaleGroupVO::getReceived_id).collect(Collectors.toList());
            // 获取合同和下面未收到款的销售单对应关系
            Map<Long, List<Long>> unReceivedBillSaleIds = result.stream().filter(item -> item.getReceived_id() == -1).collect(Collectors.groupingBy(ContractBillSaleGroupVO::getId, Collectors.mapping(ContractBillSaleGroupVO::getBill_sale_id, Collectors.toList())));
            if (CollectionUtils.isEmpty(collect4)) {
                collect4.add(-1L);
            }
            Map<Long, List<ContractBillSaleGroupVO>> resultMap = result.stream().collect(Collectors.groupingBy(ContractBillSaleGroupVO::getId));
            // 获取本年的开始时间
            LocalDateTime year_begin = DateUtil.dateToLocalDateTime(DateUtil.getCurrentYearStartTime());
            LocalDateTime year_end = DateUtil.dateToLocalDateTime(DateUtil.getCurrentYearEndTime());
            // 获取本年的开始日期
            LocalDate begin_date = DateUtil.dateToLocalDate(DateUtil.getCurrentYearStartTime());
            LocalDate end_date = DateUtil.dateToLocalDate(DateUtil.getCurrentYearEndTime());
            BigDecimal zero = BigDecimal.ZERO;
            List<Long> group_ids = result.stream().map(ContractBillSaleGroupVO::getGroup_id).collect(Collectors.toList());
            List<Long> billSaleIds = result.stream().map(ContractBillSaleGroupVO::getBill_sale_id).collect(Collectors.toList());
            if (CollectionUtils.isEmpty(billSaleIds)) {
                billSaleIds.add(-1L);
            }
            if (CollectionUtils.isEmpty(group_ids)) {
                group_ids.add(-1L);
            }
            // 查询出所有的档位
            List<Gear> gear_list = gearService.list(Wrappers.<Gear>lambdaQuery().eq(Gear::getDeleted, false).orderByAsc(Gear::getGear));
            Map<Long, Dictionary> dictionaryMap = dictionaryService.listByPCodeAll("fpxg-kplm").stream().collect(Collectors.toMap(Dictionary::getId, Function.identity()));
            // 查询合同信息
            Map<Long, ContractApplyVO> contractApplyVOMap = contractApplyService.getListByIds(list.stream().map(ContractBillSaleGroupVO::getId).collect(Collectors.toList())).stream().collect(Collectors.toMap(ContractApplyVO::getId, Function.identity()));
            // 获取到款信息
            Map<Long, List<AccountReceived>> receivedMap = accountReceivedService.list(Wrappers.<AccountReceived>lambdaQuery().in(AccountReceived::getGroup_id, group_ids).eq(AccountReceived::getDeleted, 0).eq(AccountReceived::getConfirm, true)).stream().collect(Collectors.groupingBy(AccountReceived::getGroup_id));
            // 获取到款信息
            List<AccountReceived> receivedList = accountReceivedService.list(Wrappers.<AccountReceived>lambdaQuery().in(AccountReceived::getId, collect4).eq(AccountReceived::getDeleted, 0).eq(AccountReceived::getConfirm, true));
            Map<Long, AccountReceived> received_map = receivedList.stream().collect(Collectors.toMap(AccountReceived::getId, Function.identity()));
            Map<Long, List<AccountReceived>> group_received_map = receivedList.stream().collect(Collectors.groupingBy(AccountReceived::getGroup_id));
            // 获取入库信息
            Map<Long, List<GoodsPutStorage>> putStorageMap = goodsPutStorageService.list(Wrappers.<GoodsPutStorage>lambdaQuery().in(GoodsPutStorage::getGroup_id, group_ids).isNull(GoodsPutStorage::getDeleted_at)).stream().collect(Collectors.groupingBy(GoodsPutStorage::getGroup_id));
            // 获取开票信息
            Map<Long, List<InvoiceApply>> invoiceMap = invoiceApplyService.list(Wrappers.<InvoiceApply>lambdaQuery().in(InvoiceApply::getGroup_id, group_ids).eq(InvoiceApply::getFlow_status, 4).isNull(InvoiceApply::getDeleted_at)).stream().collect(Collectors.groupingBy(InvoiceApply::getGroup_id));
            // 找出合同和组别的对应关系
            Map<Long, List<Long>> contractGroupIdsMap = result.stream().collect(Collectors.groupingBy(ContractBillSaleGroupVO::getId, Collectors.mapping(ContractBillSaleGroupVO::getGroup_id, Collectors.toList())));
            // 合同和销售单对应关系
            Map<Long, List<Long>> contract_sale_map = result.stream().collect(Collectors.groupingBy(ContractBillSaleGroupVO::getId, Collectors.mapping(ContractBillSaleGroupVO::getBill_sale_id, Collectors.toList())));
            // 销售单信息
            List<BillSaleVO> billSaleVOList = billSaleService.getListByIds(result.stream().map(ContractBillSaleGroupVO::getBill_sale_id).collect(Collectors.toList()));
            // 报销单信息
            List<ReimburseApplyVO> reimburseApplyVOList = reimburseApplyService.getListBySaleIds(billSaleIds);
            // 查询所有销售单的结算材料
            List<BillSaleAppendix> billSaleAppendices = billSaleAppendixService.list(Wrappers.<BillSaleAppendix>lambdaQuery().in(BillSaleAppendix::getBill_sale_id, billSaleIds));
            Map<Long, List<Long>> listMap = billSaleAppendices.stream().collect(Collectors.groupingBy(BillSaleAppendix::getBill_sale_id, Collectors.mapping(BillSaleAppendix::getId, Collectors.toList())));
            List<Long> contract_ids = result.stream().map(ContractBillSaleGroupVO::getId).collect(Collectors.toList());
            //  合同相关报销单信息
            List<ReimburseApplyVO> reimburseApplyVOList2 = reimburseApplyService.getListBySaleIds(contract_ids);
            // 市场单信息
            List<BillAdMakeSupplierConfirmVO> confirmList = billAdMakeSupplierConfirmService.getListBySaleIds(billSaleIds);
            // 销售单和组别的关系
            List<Long> collect3 = result.stream().map(ContractBillSaleGroupVO::getGroup_id).collect(Collectors.toList());
            if (CollectionUtils.isEmpty(collect3)) {
                collect3.add(-1L);
            }
            Map<Long, List<Long>> group_sale_relation_map = billSaleGroupRelationService.list(Wrappers.<BillSaleGroupRelation>lambdaQuery().in(BillSaleGroupRelation::getGroup_id, collect3)).stream().collect(Collectors.groupingBy(BillSaleGroupRelation::getGroup_id, Collectors.mapping(BillSaleGroupRelation::getBill_sale_id, Collectors.toList())));
            // 处理数据
            for (ContractBillSaleGroupVO contractBillSaleGroupVO : list) {
                List<ContractBillSaleGroupVO> contractBillSaleGroupVOS = resultMap.get(contractBillSaleGroupVO.getId());
                if (CollectionUtils.isNotEmpty(contractBillSaleGroupVOS)) {
                    Map<Long, List<ContractBillSaleGroupVO>> collect = contractBillSaleGroupVOS.stream().collect(Collectors.groupingBy(ContractBillSaleGroupVO::getReceived_id));
                    collect.forEach((key, value) -> {
                        resultList.add(value.get(0));
                    });
                } else {
                    resultList.add(contractBillSaleGroupVO);
                }
            }
            for (ContractBillSaleGroupVO vo : resultList) {
                List<Long> groupIds = contractGroupIdsMap.get(vo.getId());
                if (CollectionUtils.isNotEmpty(groupIds)) {
                    // 合同相关所有的到款信息
                    List<AccountReceived> accountReceivedList = new ArrayList<>();
                    // 合同相关所有的入库信息
                    List<GoodsPutStorage> goodsPutStorageList = new ArrayList<>();
                    List<InvoiceApply> invoiceApplyList = new ArrayList<>();
                    for (Long groupId : groupIds) {
                        accountReceivedList.addAll(receivedMap.getOrDefault(groupId, new ArrayList<>()));
                        goodsPutStorageList.addAll(putStorageMap.getOrDefault(groupId, new ArrayList<>()));
                        invoiceApplyList.addAll(invoiceMap.getOrDefault(groupId, new ArrayList<>()));
                    }
                    // 往年收款
                    List<AccountReceived> usualAccountReceivedList = accountReceivedList.stream().filter(item -> item.getAccount_received_time().atStartOfDay().isBefore(year_begin)).collect(Collectors.toList());
                    // 今年收款
                    List<AccountReceived> cuYearAccountReceivedList = accountReceivedList.stream().filter(item -> (item.getAccount_received_time().atStartOfDay().isAfter(year_begin) || item.getAccount_received_time().equals(year_begin)) && (item.getAccount_received_time().atStartOfDay().isBefore(year_end) || item.getAccount_received_time().equals(year_end))).collect(Collectors.toList());
                    // 往年已收物品
                    List<GoodsPutStorage> usualPutStorageList = goodsPutStorageList.stream().filter(item -> item.getCreated_at().isBefore(year_begin)).collect(Collectors.toList());
                    // 今年已收物品
                    List<GoodsPutStorage> cuYearPutStorageList = goodsPutStorageList.stream().filter(item -> (item.getCreated_at().isAfter(year_begin) || item.getCreated_at().equals(year_begin)) && (item.getCreated_at().isBefore(year_end) || item.getCreated_at().equals(year_end))).collect(Collectors.toList());
                    // 往年已收金额
                    BigDecimal amount_received_in_previous_years = BigDecimal.ZERO;
                    for (AccountReceived accountReceived : usualAccountReceivedList) {
                        amount_received_in_previous_years = amount_received_in_previous_years.add(accountReceived.getAccount());
                    }
                    // 往年置换金额
                    BigDecimal displace_amount_from_previous_years = BigDecimal.ZERO;
                    for (GoodsPutStorage goodsPutStorage : usualPutStorageList) {
                        displace_amount_from_previous_years = displace_amount_from_previous_years.add(goodsPutStorage.getTotal_price());
                    }
                    // 今年已现金金额
                    BigDecimal amount_this_year = BigDecimal.ZERO;
                    for (AccountReceived accountReceived : cuYearAccountReceivedList) {
                        amount_this_year = amount_this_year.add(accountReceived.getAccount());
                    }
                    // 今年已置换金额
                    BigDecimal displace_amount_this_year = BigDecimal.ZERO;
                    for (GoodsPutStorage goodsPutStorage : cuYearPutStorageList) {
                        displace_amount_this_year = displace_amount_this_year.add(goodsPutStorage.getTotal_price());
                    }
                    vo.setAmount_received_in_previous_years(amount_received_in_previous_years);
                    vo.setDisplace_amount_from_previous_years(displace_amount_from_previous_years);
                    vo.setDisplace_consume_amount_from_previous_years(zero);
                    vo.setAmount_this_year(amount_this_year);
                    vo.setDisplace_amount_this_year(displace_amount_this_year);
                    vo.setDisplace_consume_amount_this_year(zero);
                    vo.setAmount_of_replacement_consumption_receivable_this_year(zero);
                    // 统计开票信息
                    BigDecimal additional_amount_this_year = BigDecimal.ZERO;
                    for (InvoiceApply invoiceApply : invoiceApplyList) {
                        if (invoiceApply.getAdditional_amount() != null) {
                            additional_amount_this_year = additional_amount_this_year.add(invoiceApply.getAdditional_amount());
                        }
                    }
                    vo.setAdditional_amount_this_year(additional_amount_this_year);
                } else {
                    vo.setAmount_received_in_previous_years(zero);
                    vo.setDisplace_amount_from_previous_years(zero);
                    vo.setAmount_this_year(zero);
                    vo.setDisplace_amount_this_year(zero);
                    vo.setAdditional_amount_this_year(zero);
                }
                // 本年度应收现金金额
                BigDecimal amount_of_cash_receivable_for_this_year = BigDecimal.ZERO;
                // 今年应收置换金额
                BigDecimal amount_replacement_receivable_this_year = BigDecimal.ZERO;
                ContractApplyVO contractApplyVO = contractApplyVOMap.get(vo.getId());
                if (contractApplyVO != null) {
                    Map<Integer, List<ContractPayDetail>> collect = contractApplyVO.getPayDetailList().stream().collect(Collectors.groupingBy(ContractPayDetail::getPay_type));
                    if (collect.containsKey(1) && collect.containsKey(2)) {
                        vo.setType(3);
                    } else if (collect.containsKey(1)) {
                        vo.setType(1);
                    } else {
                        vo.setType(2);
                    }
                    // 现金付款明细
                    List<ContractPayDetail> collect1 = contractApplyVO.getPayDetailList().stream().filter(item -> (item.getPay_time().isAfter(begin_date) || item.getPay_time().equals(begin_date)) && (item.getPay_time().isBefore(end_date) || item.getPay_time().equals(end_date)) && item.getPay_type() == 1).collect(Collectors.toList());
                    for (ContractPayDetail contractPayDetail : collect1) {
                        amount_of_cash_receivable_for_this_year = amount_of_cash_receivable_for_this_year.add(contractPayDetail.getPay_amount());
                    }
                    // 置换付款明细
                    List<ContractPayDetail> collect2 = contractApplyVO.getPayDetailList().stream().filter(item -> (item.getPay_time().isAfter(begin_date) || item.getPay_time().equals(begin_date)) && (item.getPay_time().isBefore(end_date) || item.getPay_time().equals(end_date)) && item.getPay_type() == 2).collect(Collectors.toList());
                    for (ContractPayDetail contractPayDetail : collect2) {
                        amount_replacement_receivable_this_year = amount_replacement_receivable_this_year.add(contractPayDetail.getPay_amount());
                    }
                }
                vo.setAmount_of_cash_receivable_for_this_year(amount_of_cash_receivable_for_this_year);
                vo.setAmount_replacement_receivable_this_year(amount_replacement_receivable_this_year);
                vo.setDisplace_consume_amount_this_year(zero);
                vo.setDisplace_consume_amount_from_previous_years(zero);
                vo.setAmount_of_replacement_consumption_receivable_this_year(zero);
                vo.setAccountReceived(received_map.get(vo.getReceived_id()));
                List<Long> bill_sale_ids = group_sale_relation_map.getOrDefault(vo.getGroup_id(), new ArrayList<>());
                // todo 如果vo.getGroup_id = -1 则查询出合同下未到款的销售单的id
                if (vo.getGroup_id() != null && vo.getGroup_id() == -1) {
                    bill_sale_ids.clear();
                    bill_sale_ids.addAll(unReceivedBillSaleIds.getOrDefault(vo.getId(), new ArrayList<>()));
                }
                if (CollectionUtils.isNotEmpty(bill_sale_ids)) {
                    List<BillSaleVO> collect = billSaleVOList.stream().filter(item -> bill_sale_ids.contains(item.getId())).collect(Collectors.toList());
                    if (CollectionUtils.isNotEmpty(collect)) {
                        LocalDate begin = collect.get(0).getActual_execute_time_start();
                        LocalDate end = collect.get(0).getActual_execute_time_end();
                        // 媒体形式
                        List<MediaPointVO> mediaPointVOList = new ArrayList<>();
                        // 本次执行数量
                        long mediaCount = 0L;
                        // 刊例总价
                        List<MediaPointVO> mediaPriceList = new ArrayList<>();
                        // 媒体形式（单价）
                        List<MediaPointVO> media_price = new ArrayList<>();
                        // 媒体形式（分项执行金额）
                        List<MediaPointVO> itemMediaPrice = new ArrayList<>();
                        // 执行金额
                        BigDecimal execute_amount = BigDecimal.ZERO;
                        // 置换金额
                        BigDecimal displace_amount = BigDecimal.ZERO;
                        // 置换消费金额
                        BigDecimal displace_consume_amount = BigDecimal.ZERO;
                        // 计提额
                        BigDecimal accrued_amount = BigDecimal.ZERO;
                        // 代理资源金额
                        BigDecimal proxy_resource_amount = BigDecimal.ZERO;
                        // 刊例总价总和
                        BigDecimal total_price = BigDecimal.ZERO;
                        for (BillSaleVO billSaleVO : collect) {
                            if (begin.isAfter(billSaleVO.getActual_execute_time_start())) {
                                begin = billSaleVO.getActual_execute_time_start();
                            }
                            if (end.isBefore(billSaleVO.getActual_execute_time_end())) {
                                end = billSaleVO.getActual_execute_time_end();
                            }
                            // 计算媒体形式
                            MediaPointVO mediaPointVO;
                            for (BillSaleMediaAmountVO billSaleMediaAmountVO : billSaleVO.getMediaAmountVOList()) {
                                List<MediaPointVO> tmp = mediaPointVOList.stream().filter(item -> item.getMedia_type_spec_price_id().equals(billSaleMediaAmountVO.getMedia_type_spec_price_id())).collect(Collectors.toList());
                                if (CollectionUtils.isNotEmpty(tmp)) {
                                    mediaPointVO = tmp.get(0);
                                    mediaPointVO.setCount(mediaPointVO.getCount() + billSaleMediaAmountVO.getMedia_count());
                                    mediaPointVO.setPrice(mediaPointVO.getPrice().add(billSaleMediaAmountVO.getFill_amount()));
                                } else {
                                    mediaPointVO = new MediaPointVO();
                                    mediaPointVO.setMedia_type_spec_price_id(billSaleMediaAmountVO.getMedia_type_spec_price_id());
                                    mediaPointVO.setMedia_type_spec_price_name(billSaleMediaAmountVO.getName());
                                    mediaPointVO.setCount(billSaleMediaAmountVO.getMedia_count());
                                    mediaPointVO.setPrice(billSaleMediaAmountVO.getFill_amount());
                                    mediaPointVOList.add(mediaPointVO);
                                    itemMediaPrice.add(mediaPointVO);
                                }
                                mediaCount += mediaPointVO.getCount();
                            }
                            // 计算刊例总价
                            MediaPointVO mediaPointVO1;
                            for (MediaSpecTypeCountAmountVO mediaSpecTypeCountAmountVO : billSaleVO.getMediaPriceList()) {
                                List<MediaPointVO> tmp = mediaPriceList.stream().filter(item -> item.getMedia_type_spec_price_id().equals(mediaSpecTypeCountAmountVO.getMedia_type_spec_price_id())).collect(Collectors.toList());
                                if (CollectionUtils.isNotEmpty(tmp)) {
                                    mediaPointVO1 = tmp.get(0);
                                    mediaPointVO1.setCount(mediaPointVO1.getCount() + mediaSpecTypeCountAmountVO.getSpec_count().intValue());
                                    mediaPointVO1.setPrice(mediaPointVO1.getPrice().add(mediaSpecTypeCountAmountVO.getAmount()));
                                } else {
                                    mediaPointVO1 = new MediaPointVO();
                                    mediaPointVO1.setMedia_type_spec_price_id(mediaSpecTypeCountAmountVO.getMedia_type_spec_price_id());
                                    mediaPointVO1.setMedia_type_spec_price_name(mediaSpecTypeCountAmountVO.getName());
                                    mediaPointVO1.setCount(mediaSpecTypeCountAmountVO.getSpec_count().intValue());
                                    mediaPointVO1.setPrice(mediaSpecTypeCountAmountVO.getAmount());
                                    mediaPriceList.add(mediaPointVO1);
                                }
                                total_price = total_price.add(mediaPointVO1.getPrice());
                            }
                            execute_amount = execute_amount.add(billSaleVO.getExecute_amount() == null ? zero : billSaleVO.getExecute_amount());
                            // 计算消费金额和置换金额
                            displace_amount = displace_amount.add(billSaleVO.getItem_amount() == null ? zero : billSaleVO.getItem_amount());
                            displace_consume_amount = displace_consume_amount.add(billSaleVO.getCash_amount() == null ? zero : billSaleVO.getCash_amount());
                            // 计提额
                            accrued_amount = accrued_amount.add((billSaleVO.getExecute_amount() == null ? zero : billSaleVO.getExecute_amount()).multiply(new BigDecimal(StringUtils.isBlank(billSaleVO.getAccrued_amount()) ? "0" : billSaleVO.getAccrued_amount())));
                            // 计算代理资源金额
                            for (BillSaleProxy billSaleProxy : billSaleVO.getBillSaleProxies()) {
                                proxy_resource_amount = proxy_resource_amount.add(billSaleProxy.getProxy_amount() == null ? zero : billSaleProxy.getProxy_amount());
                            }
                        }
                        // 计算执行单价
                        for (MediaPointVO mediaPointVO : mediaPointVOList) {
                            MediaPointVO tmp = new MediaPointVO();
                            tmp.setMedia_type_spec_price_id(mediaPointVO.getMedia_type_spec_price_id());
                            tmp.setMedia_type_spec_price_name(mediaPointVO.getMedia_type_spec_price_name());
                            tmp.setPrice(mediaPointVO.getPrice().divide(new BigDecimal(mediaPointVO.getCount()), 2, RoundingMode.HALF_UP));
                            media_price.add(tmp);
                        }
                        // 销售单最早时间和最晚时间
                        vo.setBegin_date(begin);
                        vo.setEnd_date(end);
                        vo.setMediaPointVOList(mediaPointVOList);
                        vo.setMedia_count(mediaCount);
                        vo.setMediaPriceList(mediaPriceList);
                        vo.setMedia_price(media_price);
                        vo.setItemMediaPrice(itemMediaPrice);
                        vo.setExecute_amount(execute_amount);
                        vo.setDisplace_amount(displace_amount);
                        vo.setDisplace_consume_amount(displace_consume_amount);
                        List<InvoiceApply> invoiceApplyList = invoiceMap.get(vo.getGroup_id());
                        Boolean flag = CollectionUtils.isNotEmpty(invoiceApplyList) && invoiceApplyList.size() == 1;
                        vo.setInvoice_make_out(flag);
                        if (flag) {
                            vo.setInvoice_amount(invoiceApplyList.get(0).getInvoice_amount());
                            vo.setInvoice_category_id(invoiceApplyList.get(0).getInvoice_category_id());
                            vo.setInvoice_category_name(dictionaryMap.get(vo.getInvoice_category_id()).getName());
                            vo.setAdditional_amount(invoiceApplyList.get(0).getAdditional_amount());
                        }
                        vo.setAccrued_amount(accrued_amount);
                        Map<Long, List<ReimburseApplyVO>> reimburseApplyVOMap = reimburseApplyVOList.stream().collect(Collectors.groupingBy(ReimburseApplyVO::getBill_sale_id));
                        List<ReimburseApplyVO> reimburseApplyVOList1 = reimburseApplyVOMap.get(vo.getBill_sale_id());
                        BigDecimal actual_accrued_amount = BigDecimal.ZERO;
                        if (CollectionUtils.isNotEmpty(reimburseApplyVOList1)) {
                            actual_accrued_amount = execute_amount.subtract(reimburseApplyVOList1.get(0).getTotal_amount());
                        }
                        vo.setActual_accrued_amount(actual_accrued_amount);
                        vo.setProxy_resource_amount(proxy_resource_amount);
                        // 物料成本
                        BigDecimal material_cost = BigDecimal.ZERO;
                        for (BillAdMakeSupplierConfirmVO billAdMakeSupplierConfirmVO : confirmList.stream().filter(item -> bill_sale_ids.contains(item.getBill_sale_id())).collect(Collectors.toList())) {
                            material_cost = material_cost.add(billAdMakeSupplierConfirmVO.getTotal() == null ? BigDecimal.ZERO : billAdMakeSupplierConfirmVO.getTotal());
                        }
                        vo.setMaterial_cost(material_cost);
                        BigDecimal operating_expenses_for_this_year = BigDecimal.ZERO;
                        List<Long> sale_ids = contract_sale_map.get(vo.getId());
                        if (CollectionUtils.isNotEmpty(sale_ids)) {
                            List<ReimburseApplyVO> collect1 = reimburseApplyVOList.stream().filter(item -> sale_ids.contains(item.getBill_sale_id())).collect(Collectors.toList());
                            for (ReimburseApplyVO reimburseApplyVO : collect1) {
                                operating_expenses_for_this_year = operating_expenses_for_this_year.add(reimburseApplyVO.getTotal_amount());
                            }
                        }
                        vo.setOperating_expenses_for_this_year(operating_expenses_for_this_year);

                        // 已执行金额、未执行金额
                        // 原逻辑：已执行金额 = 已收金额
                        // 现逻辑：已执行金额 = 销售单总金额，即执行总金额
                        /*BigDecimal executed_amount = BigDecimal.ZERO;
                        List<AccountReceived> accountReceivedList = group_received_map.get(vo.getGroup_id());
                        if (CollectionUtils.isNotEmpty(accountReceivedList)) {
                            for (AccountReceived accountReceived : accountReceivedList) {
                                executed_amount = executed_amount.add(accountReceived.getAccount());
                            }
                        }
                        vo.setExecuted_amount(executed_amount);
                        vo.setUnExecuted_amount(execute_amount.subtract(executed_amount));*/
                        BigDecimal received_amount = BigDecimal.ZERO;
                        List<AccountReceived> accountReceivedList = group_received_map.get(vo.getGroup_id());
                        if (CollectionUtils.isNotEmpty(accountReceivedList)) {
                            for (AccountReceived accountReceived : accountReceivedList) {
                                received_amount = received_amount.add(accountReceived.getAccount());
                            }
                        }
                        vo.setUnReceived_amount(execute_amount.subtract(received_amount));
                        BigDecimal executed_amount = new BigDecimal(execute_amount.toString());
                        vo.setExecuted_amount(executed_amount);
                        vo.setUnExecuted_amount(execute_amount.subtract(executed_amount));


                        List<ReimburseApplyVO> collect1 = reimburseApplyVOList2.stream().filter(item -> contract_ids.contains(item.getId())).collect(Collectors.toList());
                        BigDecimal customer_maintenance_fees = BigDecimal.ZERO;
                        for (ReimburseApplyVO reimburseApplyVO : collect1) {
                            customer_maintenance_fees = customer_maintenance_fees.add(reimburseApplyVO.getTotal_amount());
                        }
                        vo.setCustomer_maintenance_fees(customer_maintenance_fees);
                        if (vo.getGroup_id() == -1L) {
                            vo.setCommission(zero);
                            vo.setCompany_maintenance_fee(zero);
                        } else {
                            // 公司客户按照1%计算提成
                            if (boss_ids.contains(vo.getPerson_in_charge_id())) {
                                vo.setCommission(execute_amount.multiply(new BigDecimal("0.01")));
                                // 客提
                                if (CollectionUtils.isNotEmpty(reimburseApplyVOList1)) {
                                    vo.setCompany_maintenance_fee(reimburseApplyVOList1.get(0).getTotal_amount());
                                } else {
                                    vo.setCompany_maintenance_fee(zero);
                                }
                            } else {
                                // 计算出折扣
                                BigDecimal source_discount = execute_amount.divide(total_price, 4, RoundingMode.HALF_UP);
                                // 从小到大遍历档位 找出档位
                                CustomerCommissionVO commissionVO = new CustomerCommissionVO();
                                for (Gear gear : gear_list) {
                                    if (source_discount.compareTo(gear.getTransaction_discount()) < 0) { // 表示是此档位
                                        commissionVO.setSource_gear(gear);
                                        commissionVO.setMax_quota(execute_amount.multiply(gear.getMaintenance_proportion()));
                                        break;
                                    }
                                }
                                if (commissionVO.getMax_quota() == null && commissionVO.getSource_gear() == null) {// 没有匹配的档位
                                    vo.setCommission(zero);
                                }
                                // 判断是否超过最大 客提额度
                                BigDecimal totalAmount = null;
                                if (reimburseApplyVOList1 != null && reimburseApplyVOList1.get(0) != null)
                                    totalAmount = reimburseApplyVOList1.get(0).getTotal_amount();
                                if (totalAmount == null || totalAmount.compareTo(commissionVO.getMax_quota()) <= 0) {
                                    vo.setCommission(commissionVO.getMax_quota().multiply(execute_amount));
                                } else {
                                    // 计算出超出的部分
                                    BigDecimal subtract = totalAmount.subtract(commissionVO.getMax_quota());
                                    BigDecimal total_execute_amount = execute_amount.subtract(subtract);
                                    // 计算出真实的折扣
                                    BigDecimal real_discount = total_execute_amount.divide(total_price, 4, RoundingMode.HALF_UP);
                                    for (Gear gear : gear_list) {
                                        if (real_discount.compareTo(gear.getTransaction_discount()) < 0) { // 表示是此档位
                                            commissionVO.setReal_gear(gear);
                                            break;
                                        }
                                    }
                                    if (commissionVO.getReal_gear() == null) {
                                        vo.setCommission(zero);
                                        continue;
                                    }
                                    vo.setCommission(commissionVO.getMax_quota().multiply(subtract));
                                }
                            }
                        }
                        boolean f = true;
                        for (Long billSaleId : bill_sale_ids) {
                            if (listMap.get(billSaleId) == null) {
                                f = false;
                                break;
                            }
                        }
                        if (f) {
                            vo.setHas_material(f);
                        }
                    }
                }
            }
        }
    }

    @Override
    public boolean receiveContract(Long id) {
        return contractApplyService.update(Wrappers.<ContractApply>lambdaUpdate().set(ContractApply::getReceive_contract, true).eq(ContractApply::getId, id));
    }

    @Override
    public boolean fill(Long id, String no) {
        return contractApplyService.update(Wrappers.<ContractApply>lambdaUpdate().set(ContractApply::getContract_filing_no, no).eq(ContractApply::getId, id));
    }

    @Override
    public boolean remind(Long id, Long group_id) {
        ContractApplyVO contractApplyVO = contractApplyService.getOneById(id);
        BillSaleGroup group = billSaleGroupService.getById(group_id);
        CustomerInfo customerInfo = customerInfoService.getById(contractApplyVO.getCustomer_id());
        if (customerInfo.getMaintenance_staff_id() != null) {
            MessageCenter messageCenter = new MessageCenter();
            messageCenter.setType(MessageCenterTypeEnum.TIP.getType());
            messageCenter.setTitle("催款提醒");
            messageCenter.setContent(customerInfo.getName() + "客户的编号为：" + contractApplyVO.getContract_num() + "甲方名称是" + contractApplyVO.getPart_a() + "的合同下的" + group.getName() + "组有款项未到款，财务发起了催款提醒！");
            messageCenter.setUser_id(customerInfo.getMaintenance_staff_id());
            messageCenter.setCreated_at(LocalDateTime.now());
            return messageCenterService.save(messageCenter);
        } else {
            throw new CommonException("客户没有维护人！");
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean updateDate(LocalDate date, Long groupId) {
        List<BillSaleGroupRelation> list = billSaleGroupRelationService.list(Wrappers.<BillSaleGroupRelation>lambdaQuery().eq(BillSaleGroupRelation::getGroup_id, groupId));
        List<Long> ids = list.stream().map(BillSaleGroupRelation::getBill_sale_id).collect(Collectors.toList());
        return billSaleService.updateDate(date, ids);
    }

    @Override
    public void export(HttpServletResponse response, Long customerId, String contractNum, LocalDate contractBeginDateStart, LocalDate contractBeginDateEnd, LocalDate contractEndDateStart, LocalDate contractEndDateEnd) throws IOException, InvalidFormatException {
        List<ContractBillSaleGroupVO> list = financialSummaryMapper.exportList(customerId, contractNum, contractBeginDateStart, contractBeginDateEnd, contractEndDateStart, contractEndDateEnd);
        List<ContractBillSaleGroupVO> resultList = new ArrayList<>();
        handle(list, resultList);
        writeExcel(resultList, response);
    }

    private void writeExcel(List<ContractBillSaleGroupVO> data, HttpServletResponse response) throws IOException {
        DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        InputStream inputStream = null;
        XSSFWorkbook workbook = null;
        ServletOutputStream out = null;
        try {
            ClassPathResource resource = new ClassPathResource("template" + File.separator + "tmp.xlsx");
            inputStream = resource.getInputStream();
            // 兼容开发环境
            workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheet("Sheet1");
            // 写入表格头
            writeExcelHeader(sheet);
            // 全局样式
            XSSFCellStyle publicStyle = workbook.createCellStyle();
            publicStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            publicStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            // 换行样式
            XSSFCellStyle warpStyle = workbook.createCellStyle();
            warpStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            warpStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            warpStyle.setWrapText(true);
            // 从第二行开始写入
            for (int i = 0; i < data.size(); i++) {
                ContractBillSaleGroupVO vo = data.get(i);
                Row row = sheet.createRow(2 + i);
                for (int k = 0; k < 51; k++) {
                    Cell cell = row.createCell(k);
                    cell.setCellStyle(publicStyle);
                }
                Cell type = row.getCell(0);
                type.setCellValue(vo.getType() == 1 ? "现金" : vo.getType() == 2 ? "置换" : "半现金半置换");
                Cell industry_name = row.getCell(1);
                industry_name.setCellValue(vo.getIndustry_name());
                Cell contract_date = row.getCell(2);
                contract_date.setCellValue(vo.getContract_begin_date() == null ? "" : dateFormatter.format(vo.getContract_begin_date()) + "~" + dateFormatter.format(vo.getContract_end_date()));
                Cell execution_date = row.getCell(3);
                execution_date.setCellValue(vo.getBegin_date() == null ? "" : dateFormatter.format(vo.getBegin_date()) + "~" + dateFormatter.format(vo.getEnd_date()));
                // 合同名称
                Cell party_a = row.getCell(4);
                party_a.setCellValue(vo.getParty_a());
                Cell customer_name = row.getCell(5);
                customer_name.setCellValue(vo.getCustomer_name());
                Cell company_head = row.getCell(6);
                company_head.setCellValue(vo.getCompany_head());
                Cell contract_num = row.getCell(7);
                contract_num.setCellValue(vo.getContract_num());
                Cell receive_contract = row.getCell(8);
                receive_contract.setCellValue(vo.getReceive_contract() ? "是" : "否");
                Cell contract_filing_no = row.getCell(9);
                contract_filing_no.setCellValue(vo.getContract_filing_no());
                Cell person_in_charge_name = row.getCell(10);
                person_in_charge_name.setCellValue(vo.getPerson_in_charge_name());
                Cell maintenance_staff_name = row.getCell(11);
                maintenance_staff_name.setCellValue(vo.getMaintenance_staff_name());
                Cell group_name = row.getCell(12);
                group_name.setCellValue(vo.getGroup_name());
                Cell accountReceived = row.getCell(13);
                if (vo.getAccountReceived() != null) {
                    accountReceived.setCellValue(dateTimeFormatter.format(vo.getAccountReceived().getAccount_received_time()) + ":" + vo.getAccountReceived().getAccount());
                } else {
                    accountReceived.setCellValue("");
                }
                // 媒体形式
                Cell mediaPointVOList = row.getCell(14);// todo 特殊样式
                mediaPointVOList.setCellStyle(warpStyle);
                if (CollectionUtils.isNotEmpty(vo.getItemMediaPrice())) {
                    StringBuilder s = new StringBuilder();
                    for (int j = 0; j < vo.getItemMediaPrice().size(); j++) {
                        MediaPointVO item = vo.getItemMediaPrice().get(j);
                        if (j == vo.getItemMediaPrice().size() - 1) {
                            s.append(item.getMedia_type_spec_price_name()).append("：").append(item.getCount()).append("个").append("￥").append(item.getPrice());
                        } else {
                            s.append(item.getMedia_type_spec_price_name()).append("：").append(item.getCount()).append("个").append("￥").append(item.getPrice()).append("\r\n");
                        }
                    }
                    mediaPointVOList.setCellValue(s.toString());
                } else {
                    mediaPointVOList.setCellValue("");
                }
                Cell media_count = row.getCell(15);
                media_count.setCellValue(vo.getMedia_count() != null ? vo.getMedia_count() : 0);
                // 刊例总价
                Cell mediaPriceList = row.getCell(16);// todo 特殊样式
                mediaPriceList.setCellStyle(warpStyle);
                if (CollectionUtils.isNotEmpty(vo.getMediaPriceList())) {
                    StringBuilder s = new StringBuilder();
                    for (int j = 0; j < vo.getMediaPriceList().size(); j++) {
                        MediaPointVO item = vo.getMediaPriceList().get(j);
                        if (j == vo.getMediaPriceList().size() - 1) {
                            s.append(item.getMedia_type_spec_price_name()).append("：").append(item.getCount()).append("个").append("￥").append(item.getPrice());
                        } else {
                            s.append(item.getMedia_type_spec_price_name()).append("：").append(item.getCount()).append("个").append("￥").append(item.getPrice()).append("\r\n");
                        }
                    }
                    mediaPriceList.setCellValue(s.toString());
                } else {
                    mediaPriceList.setCellValue("");
                }
                // 折扣
                Cell discount = row.getCell(17);
                discount.setCellValue(vo.getDiscount() == null ? "" : vo.getDiscount() + "%");
                // 单价
                Cell media_price = row.getCell(18);// todo 特殊样式
                media_price.setCellStyle(warpStyle);
                if (CollectionUtils.isNotEmpty(vo.getMedia_price())) {
                    StringBuilder s = new StringBuilder();
                    for (int j = 0; j < vo.getMedia_price().size(); j++) {
                        MediaPointVO item = vo.getMedia_price().get(j);
                        if (j == vo.getMedia_price().size() - 1) {
                            s.append(item.getMedia_type_spec_price_name()).append("￥").append(item.getPrice());
                        } else {
                            s.append(item.getMedia_type_spec_price_name()).append("￥").append(item.getPrice()).append("\r\n");
                        }
                    }
                    media_price.setCellValue(s.toString());
                } else {
                    media_price.setCellValue("");
                }
                // 合同金额
                Cell contract_amount = row.getCell(19);
                fillBigDecimal(vo.getContract_amount(), contract_amount);
                // 分项执行金额
                Cell itemMediaPrice = row.getCell(20);// todo 特殊样式
                itemMediaPrice.setCellStyle(warpStyle);
                itemMediaPrice.setCellValue(mediaPointVOList.getStringCellValue());
                // 执行总金额
                Cell execute_amount = row.getCell(21);
                fillBigDecimal(vo.getExecute_amount(), execute_amount);
                Cell displace_amount = row.getCell(22);
                fillBigDecimal(vo.getDisplace_amount(), displace_amount);
                Cell displace_consume_amount = row.getCell(23);
                fillBigDecimal(vo.getDisplace_consume_amount(), displace_consume_amount);
                Cell executed_amount = row.getCell(24);
                fillBigDecimal(vo.getExecuted_amount(), executed_amount);
                Cell unExecuted_amount = row.getCell(25);
                fillBigDecimal(vo.getUnExecuted_amount(), unExecuted_amount);
                // 是否开票
                Cell invoice_make_out = row.getCell(26);
                invoice_make_out.setCellValue(vo.getInvoice_make_out() != null && vo.getInvoice_make_out() ? "是" : "否");
                Cell invoice_amount = row.getCell(27);
                fillBigDecimal(vo.getInvoice_amount(), invoice_amount);
                Cell invoice_category_name = row.getCell(28);
                invoice_category_name.setCellValue(vo.getInvoice_category_name());
                Cell additional_amount = row.getCell(29);
                fillBigDecimal(vo.getAdditional_amount(), additional_amount);
                // 往年已收金额
                Cell amount_received_in_previous_years = row.getCell(30);
                fillBigDecimal(vo.getAmount_this_year(), amount_received_in_previous_years);
                Cell displace_amount_from_previous_years = row.getCell(31);
                fillBigDecimal(vo.getDisplace_amount_from_previous_years(), displace_amount_from_previous_years);
                Cell displace_consume_amount_from_previous_years = row.getCell(32);
                fillBigDecimal(vo.getDisplace_consume_amount_from_previous_years(), displace_consume_amount_from_previous_years);
                Cell amount_this_year = row.getCell(33);
                fillBigDecimal(vo.getAmount_this_year(), amount_this_year);
                Cell displace_amount_this_year = row.getCell(34);
                fillBigDecimal(vo.getDisplace_amount_this_year(), displace_amount_this_year);
                Cell displace_consume_amount_this_year = row.getCell(35);
                fillBigDecimal(vo.getDisplace_consume_amount_this_year(), displace_consume_amount_this_year);
                Cell additional_amount_this_year = row.getCell(36);
                fillBigDecimal(vo.getAdditional_amount_this_year(), additional_amount_this_year);
                Cell amount_of_cash_receivable_for_this_year = row.getCell(37);
                fillBigDecimal(vo.getAmount_of_cash_receivable_for_this_year(), amount_of_cash_receivable_for_this_year);
                Cell amount_replacement_receivable_this_year = row.getCell(38);
                fillBigDecimal(vo.getAmount_replacement_receivable_this_year(), amount_replacement_receivable_this_year);
                Cell amount_of_replacement_consumption_receivable_this_year = row.getCell(39);
                fillBigDecimal(vo.getAmount_of_replacement_consumption_receivable_this_year(), amount_of_replacement_consumption_receivable_this_year);
                // 结算材料
                Cell has_material = row.getCell(40);
                has_material.setCellValue(vo.getHas_material() != null && vo.getHas_material() ? "是" : "否");
                Cell collection_time = row.getCell(41);
                collection_time.setCellValue(vo.getCollection_time() == null ? "" : dateFormatter.format(vo.getCollection_time()));
                Cell latest_collection_time = row.getCell(42);
                latest_collection_time.setCellValue(vo.getLatest_collection_time() == null ? "" : dateFormatter.format(vo.getLatest_collection_time()));
                Cell accrued_amount = row.getCell(43);
                fillBigDecimal(vo.getAccrued_amount(), accrued_amount);
                Cell actual_accrued_amount = row.getCell(44);
                fillBigDecimal(vo.getActual_accrued_amount(), actual_accrued_amount);
                Cell proxy_resource_amount = row.getCell(45);
                fillBigDecimal(vo.getProxy_resource_amount(), proxy_resource_amount);
                Cell material_cost = row.getCell(46);
                fillBigDecimal(vo.getMaterial_cost(), material_cost);
                Cell operating_expenses_for_this_year = row.getCell(47);
                fillBigDecimal(vo.getOperating_expenses_for_this_year(), operating_expenses_for_this_year);
                Cell commission = row.getCell(48);
                fillBigDecimal(vo.getCommission(), commission);
                Cell company_maintenance_fee = row.getCell(49);
                fillBigDecimal(vo.getCompany_maintenance_fee(), company_maintenance_fee);
                Cell customer_maintenance_fees = row.getCell(50);
                fillBigDecimal(vo.getCustomer_maintenance_fees(), customer_maintenance_fees);
            }
            // 利用map找出跨行数
            Map<Long, Integer> map = new HashMap<>();
            Map<String, Integer> group_map = new HashMap<>();
            data.forEach(item -> {
                if (map.containsKey(item.getId())) {
                    map.put(item.getId(), map.get(item.getId()) + 1);
                } else {
                    map.put(item.getId(), 1);
                }
                String key = item.getId() + "_" + item.getGroup_id();
                if (group_map.containsKey(key)) {
                    group_map.put(key, group_map.get(key) + 1);
                } else {
                    group_map.put(key, 1);
                }
            });
            int[] sale_arr = new int[]{13, 25, 26, 27, 28, 29, 30, 43, 44, 45, 50, 51};
            int[] arr = new int[]{1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40};
            // 合并单元格
            for (int i = 0; i < data.size(); i++) {
                ContractBillSaleGroupVO vo = data.get(i);
                if (map.containsKey(vo.getId())) {
                    Integer num = map.get(vo.getId());
                    if (num > 1) {
                        for (int colIndex : arr) {
                            CellRangeAddress cellRangeAddress = new CellRangeAddress(2 + i, 2 + i + num - 1, colIndex - 1, colIndex - 1);
                            sheet.addMergedRegion(cellRangeAddress);
                        }
                    }
                    map.remove(vo.getId());
                }
                String key = vo.getId() + "_" + vo.getGroup_id();
                if (group_map.containsKey(key)) {
                    Integer num = group_map.get(key);
                    if (num > 1) {
                        for (int colIndex : sale_arr) {
                            CellRangeAddress cellRangeAddress = new CellRangeAddress(2 + i, 2 + i + num - 1, colIndex - 1, colIndex - 1);
                            sheet.addMergedRegion(cellRangeAddress);
                        }
                    }
                    group_map.remove(key);
                }
            }
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("test.xlsx", "UTF-8"));
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                workbook.close();
            }
            if (out != null) {
                out.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }

    private void fillBigDecimal(BigDecimal decimal, Cell cell) {
        cell.setCellValue(decimal == null ? "" : decimal.toString());
    }

    private void writeExcelHeader(Sheet sheet) {
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(33);
        LocalDate now = LocalDate.now();
        cell.setCellValue(now.getYear() + "收款信息");
    }
}
